﻿using System;
using System.Data;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Xml.Linq;
using System.Data;
using System.Data.SqlClient;
using System.Security.Cryptography;
using System.Text;

namespace MasApp.DbUtil
{
    public class MsSql : Db
    {
        private SqlCommand cmd = null;

        public SqlCommand Cmd
        {
            get
            {
                cmd = this.conn();
                return cmd;
            }
            set { cmd = value; }
        }

        public MsSql()
        {
            this.dbType = dbTypes.SqlClient;
        }

        /// <summary>
        /// 初始化连接
        /// </summary>
        /// <returns></returns>
        public SqlCommand conn()
        {
            if (this.cmd == null)
            {
                this.cmd = new SqlCommand();
                this.cmd.Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["app_SqlClient"].ConnectionString);
            }
            return this.cmd;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="parameterName"></param>
        /// <param name="value"></param>
        /// <param name="column_type">SqlDbType</param>
        public void AddParam(string parameterName, object value, string? column_type, int? size, string? sourceColumn)
        {
            column_type = column_type ?? string.Empty;
            size = size??-1;
            sourceColumn = sourceColumn ?? string.Empty;
            if (column_type == string.Empty)
            {
                Cmd.Parameters.AddWithValue(parameterName, value);
            }
            else if (size != -1)
            {
                Cmd.Parameters.Add(parameterName, column_type, size);
            }
            else if (size != -1)
            {
                Cmd.Parameters.Add(parameterName, column_type, size,sourceColumn);
            }
            else
            {
                Cmd.Parameters.Add(parameterName, column_type).Value = value;
            }
        }

        public static int login(string username, string password)
        {
            string cons = ConfigurationManager.ConnectionStrings["app"].ConnectionString;
            SqlConnection con = new SqlConnection(cons);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "select users.userid from login, users  where login.username=users.username and login.username=@us and login.password=@pass and login.available = @avai";
            cmd.Parameters.Add("@us", SqlDbType.VarChar).Value = username;
            cmd.Parameters.Add("@pass", SqlDbType.VarChar).Value = Encoding.UTF8.GetString(MD5.Create(password).Hash);
            cmd.Parameters.Add("@avai", SqlDbType.Bit).Value = 1;
            cmd.Connection.Open();
            SqlDataReader dr = cmd.ExecuteReader();
            int userid;
            if (dr.Read())
            {
                userid = dr.IsDBNull(0) ? 0 : dr.GetInt32(0);
                dr.Close();
                cmd.Connection.Close();
                return userid;
            }
            else
            {
                dr.Close();
                cmd.Connection.Close();
                return 0;
            }

        }
        public static SqlDataReader login(int userid)
        {
            string cons = ConfigurationManager.ConnectionStrings["app"].ConnectionString;
            SqlConnection con = new SqlConnection(cons);
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            cmd.CommandText = "SELECT role.permission FROM role INNER JOIN userrole ON role.roleid = userrole.roleid WHERE (userrole.userid = @uid)";
            cmd.Parameters.Add("@uid", SqlDbType.Int).Value = userid;
            cmd.Connection.Open();
            //CommandBehavior.CloseConnection参数只是关闭Reader对象是关闭与其关联的Connection对象
            SqlDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);
            return dr;
        }
        public static void BackUpDatabase(string s)
        {
            SqlCommand cmd = conn();
            cmd.CommandText = s;
            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                cmd.Connection.Close();
            }
            catch (Exception ee) { }
        }
    }
}